﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using NetDiskPRJ.DBUtility;
using System.Collections.Generic;//Please add references
namespace NetDiskPRJ.DAL
{
    /// <summary>
    /// 数据访问类:PrvUser
    /// </summary>
    public partial class PrvUser
    {
        public PrvUser()
        { }
        /// <summary>
        /// 是否存在该记录
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date> 
        /// </summary>
        /// <param name="logID"></param>
        /// <returns></returns>
        public bool Exists(string logID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_PrvUser");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};
            parameters[0].Value = logID;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 获得数据列表
        /// <creator>zhongyl</creator>
        /// <date>2013-02-18</date> 
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FLogID,FUserName,Fpassword,FLastLoginTime,FLastLoginIPAddr,FLoginCnt,FProhibitLogin,FForeverAvailable,FAvailableDate,FAppVersionNo ");
            strSql.Append(" FROM t_PrvUser ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 更新密码
        /// <creator>zhongyl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <param name="logid">登录ID</param>
        /// <param name="password">MD5加密后的密码</param>
        /// <returns></returns>
        public bool UpdatePassword(string logid, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_PrvUser set ");
            strSql.Append("Fpassword=@Fpassword");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Fpassword", SqlDbType.VarChar,254),
					new SqlParameter("@FLogID", SqlDbType.VarChar,30)};
            parameters[0].Value = password;
            parameters[1].Value = logid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取用户密码
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date> 
        /// </summary>
        /// <param name="logid">登录ID</param>
        /// <returns></returns>
        public string GetUserPassword(string logID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Fpassword FROM t_PrvUser");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};
            parameters[0].Value = logID;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 删除一条数据
        /// <creator>zhongyl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        public void Delete(string FLogID)
        {
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from t_PrvUserGroup ");
            strSql.Append(" where FLogID=@FLogID");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)		};
            parameters[0].Value = FLogID;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("delete from t_PrvUser ");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters2 = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)			};
            parameters2[0].Value = FLogID;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);


            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("delete from t_PrvAllowData ");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters3 = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)			};
            parameters3[0].Value = FLogID;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters3;
            cmdList.Add(cmds);

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithIndentity(cmdList);
            }
            
        }
        /// <summary>
        /// 保存方法
        /// <creator>zhongyl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <param name="AddPrvUser"></param>
        /// <param name="UpdatePrvUser"></param>
        /// <param name="PrvUserGroupList"></param>
        public void SavePrvUser(List<Model.PrvUser> AddPrvUser, List<Model.PrvUser> UpdatePrvUser, List<Model.PrvUserGroup> PrvUserGroupList, List<Model.PrvAllowData> PrvAllowDataList)
        {
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            StringBuilder strSql = new StringBuilder();


            foreach (Model.PrvUser model in AddPrvUser)
            {
                model.Fpassword = NetDiskPRJ.Common.MD5.MD5Encrypt(model.FLogID.ToLower() + "");
                cmds = new CommandInfo();
                strSql = new StringBuilder();
                strSql.Append("insert into t_PrvUser(");
                strSql.Append("FLogID,FUserName,Fpassword,FProhibitLogin,FForeverAvailable,FAvailableDate)");
                strSql.Append(" values (");
                strSql.Append("@FLogID,@FUserName,@Fpassword,@FProhibitLogin,@FForeverAvailable,@FAvailableDate)");
                SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@Fpassword", SqlDbType.NVarChar,255),
					new SqlParameter("@FProhibitLogin", SqlDbType.VarChar,1),
					new SqlParameter("@FForeverAvailable", SqlDbType.VarChar,1),
					new SqlParameter("@FAvailableDate", SqlDbType.DateTime)};
                parameters[0].Value = model.FLogID;
                parameters[1].Value = model.FUserName;
                parameters[2].Value = model.Fpassword;
                parameters[3].Value = model.FProhibitLogin;
                parameters[4].Value = model.FForeverAvailable;
                parameters[5].Value = model.FAvailableDate;

                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters;
                cmdList.Add(cmds);
            }

            foreach (Model.PrvUser model in UpdatePrvUser)
            {
                cmds = new CommandInfo();
                strSql = new StringBuilder();
                strSql.Append("update t_PrvUser set ");
                strSql.Append("FUserName=@FUserName,");
                strSql.Append("FProhibitLogin=@FProhibitLogin,");
                strSql.Append("FForeverAvailable=@FForeverAvailable,");
                strSql.Append("FAvailableDate=@FAvailableDate");
                strSql.Append(" where FLogID=@FLogID ");
                SqlParameter[] parameters = {
					new SqlParameter("@FUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@FProhibitLogin", SqlDbType.VarChar,1),
					new SqlParameter("@FForeverAvailable", SqlDbType.VarChar,1),
					new SqlParameter("@FAvailableDate", SqlDbType.DateTime),
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};
                parameters[0].Value = model.FUserName;
                parameters[1].Value = model.FProhibitLogin;
                parameters[2].Value = model.FForeverAvailable;
                parameters[3].Value = model.FAvailableDate;
                parameters[4].Value = model.FLogID;

                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters;
                cmdList.Add(cmds);
            }

            List<SqlParameter> aa = new List<SqlParameter>();
            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("delete from t_PrvUserGroup ");
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = aa.ToArray();
            cmdList.Add(cmds);

            foreach (Model.PrvUserGroup model in PrvUserGroupList)
            {
                cmds = new CommandInfo();
                strSql = new StringBuilder();
                strSql.Append("insert into t_PrvUserGroup(");
                strSql.Append("FLogID,FGroupNo)");
                strSql.Append(" values (");
                strSql.Append("@FLogID,@FGroupNo)");
                SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)};
                parameters[0].Value = model.FLogID;
                parameters[1].Value = model.FGroupNo;

                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters;
                cmdList.Add(cmds);
            }

            List<SqlParameter> aa2 = new List<SqlParameter>();
            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("delete from t_PrvAllowData ");
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = aa2.ToArray();
            cmdList.Add(cmds);

            foreach (Model.PrvAllowData model in PrvAllowDataList)
            {
                cmds = new CommandInfo();
                strSql = new StringBuilder();
                strSql.Append("insert into t_PrvAllowData(");
                strSql.Append("FTaxUnitCd,FLogID)");
                strSql.Append(" values (");
                strSql.Append("@FTaxUnitCd,@FLogID)");
                SqlParameter[] parameters = {
					new SqlParameter("@FTaxUnitCd", SqlDbType.NVarChar,16),
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};
                parameters[0].Value = model.FTaxUnitCd;
                parameters[1].Value = model.FLogID;

                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters;
                cmdList.Add(cmds);
            }

            

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithIndentity(cmdList);
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NetDiskPRJ.Model.PrvUser DataRowToModel(DataRow row)
        {
            NetDiskPRJ.Model.PrvUser model = new NetDiskPRJ.Model.PrvUser();
            if (row != null)
            {
                if (row["FLogID"] != null)
                {
                    model.FLogID = row["FLogID"].ToString();
                }
                if (row["FUserName"] != null)
                {
                    model.FUserName = row["FUserName"].ToString();
                }
                if (row["Fpassword"] != null)
                {
                    model.Fpassword = row["Fpassword"].ToString();
                }
                if (row["FLastLoginTime"] != null && row["FLastLoginTime"].ToString() != "")
                {
                    model.FLastLoginTime = DateTime.Parse(row["FLastLoginTime"].ToString());
                }
                if (row["FLastLoginIPAddr"] != null)
                {
                    model.FLastLoginIPAddr = row["FLastLoginIPAddr"].ToString();
                }
                if (row["FLoginCnt"] != null && row["FLoginCnt"].ToString() != "")
                {
                    model.FLoginCnt = decimal.Parse(row["FLoginCnt"].ToString());
                }
                if (row["FProhibitLogin"] != null)
                {
                    model.FProhibitLogin = row["FProhibitLogin"].ToString();
                }
                if (row["FForeverAvailable"] != null)
                {
                    model.FForeverAvailable = row["FForeverAvailable"].ToString();
                }
                if (row["FAvailableDate"] != null && row["FAvailableDate"].ToString() != "")
                {
                    model.FAvailableDate = DateTime.Parse(row["FAvailableDate"].ToString());
                }
                if (row["FAppVersionNo"] != null)
                {
                    model.FAppVersionNo = row["FAppVersionNo"].ToString();
                }
            }
            return model;
        }
        /// <summary>
        /// 更新登录相关信息
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <param name="logID">登录ID</param>
        /// <param name="version">版本信息</param>
        /// <param name="ipaddr">IP地址</param>
        public void UpdateLoginInfo(string logID, string version, string ipaddr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE t_PrvUser SET ");
            strSql.Append("FLastLoginIPAddr=@FLastLoginIPAddr,");
            strSql.Append("FAppVersionNo=@FAppVersionNo,");
            strSql.Append("FLoginCnt = FLoginCnt + 1,");
            strSql.Append("FLastLoginTime = getdate()");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
                    new SqlParameter("@FLastLoginIPAddr", SqlDbType.NVarChar,15),
                    new SqlParameter("@FAppVersionNo", SqlDbType.VarChar,30) 
                                        };
            parameters[0].Value = logID;
            parameters[1].Value = ipaddr;
            parameters[2].Value = version;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

        }
        /// <summary>
        /// 获取用户名
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <param name="logID">登录ID</param>
        /// <returns></returns>
        public string GetUserFullname(string logID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT FUserName FROM t_PrvUser");
            strSql.Append(" where FLogID=@FLogID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};
            parameters[0].Value = logID;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 获取工作量报告
        /// <creator>sunlj</creator>
        /// <date>2013-08-02</date>
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataSet GetKPIReport(string beginDate,string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT  ( CASE WHEN payerAdd.FInputUserName IS NOT NULL
               THEN payerAdd.FInputUserName
               WHEN payerEdit.FUpdUserName IS NOT NULL
               THEN payerEdit.FUpdUserName
               WHEN projectAdd.FInputUserName IS NOT NULL
               THEN projectAdd.FInputUserName
               WHEN projectEdit.FUpdUserName IS NOT NULL
               THEN projectEdit.FUpdUserName
               WHEN preFormInput.FInputUserName IS NOT NULL
               THEN preFormInput.FInputUserName
               WHEN preFormInvalid.FInvalidUserName IS NOT NULL
               THEN preFormInvalid.FInvalidUserName
               WHEN preFormRed.FRedUserName IS NOT NULL
               THEN preFormRed.FRedUserName
               WHEN InvoiceInput.FInputUserName IS NOT NULL
               THEN InvoiceInput.FInputUserName
               WHEN InvoiceInvalid.FInvalidUserName IS NOT NULL
               THEN InvoiceInvalid.FInvalidUserName
               WHEN InvoiceRed.FRedUserName IS NOT NULL
               THEN InvoiceRed.FRedUserName
            END ) AS FName ,
            FTaxPayerInputCount ,
            (FTaxPayerUpdateCount-FTaxPayerInputCount) AS FTaxPayerUpdateCount ,
            FProjectInputCount ,
            (FProjectUpdateCount-FProjectInputCount) AS FProjectUpdateCount,
            FTaxPreFormInputCount ,
            FTaxPreFormInvalidCount ,
            FTaxPreFormRedCount ,
            FInvoiceFormInputCount ,
            FInvoiceFormInvalidCount ,
            FInvoiceFormRedCount
            FROM    (
    --纳税人新增
            ( SELECT  count(*) AS FTaxPayerInputCount ,
            FInputUserName
            FROM    dbo.t_TaxPayer WHERE 1=1 ");
            if (beginDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '"+beginDate+"', FInputDate) >= 0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '"+endDate+"', FInputDate) <= 0 ");
            }
            strSql.Append(@" GROUP BY FInputUserName
            ) payerAdd
          FULL JOIN 
    --纳税人修改
          ( SELECT  count(*) AS FTaxPayerUpdateCount ,
                    FUpdUserName
            FROM    dbo.t_TaxPayer
            WHERE 1=1");
            if (beginDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FUpdDate) >= 0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FUpdDate) <= 0 ");
            }
            strSql.Append(@" GROUP BY FUpdUserName
          ) payerEdit ON payerAdd.FInputUserName = payerEdit.FUpdUserName
          FULL JOIN 
    --项目新增
          ( SELECT  count(*) AS FProjectInputCount ,
                    FInputUserName
            FROM    dbo.t_Project WHERE 1=1 ");
            if (beginDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FInputDate) >= 0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FInputDate) <= 0 ");
            }
            strSql.Append(@" GROUP BY FInputUserName
          ) projectAdd ON payerAdd.FInputUserName = projectAdd.FInputUserName
                          OR payerEdit.FUpdUserName = projectAdd.FInputUserName
          FULL JOIN 
    --项目修改
          ( SELECT  count(*) AS FProjectUpdateCount ,
                    FUpdUserName
            FROM    dbo.t_Project WHERE 1=1 ");
            if (beginDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FUpdDate) >= 0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FUpdDate) <= 0 ");
            }
            strSql.Append(@" GROUP BY FUpdUserName
          ) projectEdit ON payerAdd.FInputUserName = projectEdit.FUpdUserName
                           OR payerEdit.FUpdUserName = projectEdit.FUpdUserName
                           OR projectAdd.FInputUserName = projectEdit.FUpdUserName
          FULL JOIN 
    --纳税申报正常
          ( SELECT  count(*) AS FTaxPreFormInputCount ,
                    FInputUserName
            FROM    dbo.t_TaxPreForm WHERE 1=1 ");
            if (beginDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FInputDate) >= 0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FInputDate) <= 0 ");
            }
           strSql.Append(@" GROUP BY FInputUserName
          ) preFormInput ON payerAdd.FInputUserName = preFormInput.FInputUserName
                            OR payerEdit.FUpdUserName = preFormInput.FInputUserName
                            OR projectAdd.FInputUserName = preFormInput.FInputUserName
                            OR projectEdit.FUpdUserName = preFormInput.FInputUserName
          FULL JOIN 
    --纳税申报作废
          ( SELECT  count(*) AS FTaxPreFormInvalidCount ,
                    FInvalidUserName
            FROM    dbo.t_TaxForm WHERE 1=1 ");
           if (beginDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FInvalidDate) >= 0 ");
           }
           if (endDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FInvalidDate) <= 0 ");
           }
            strSql.Append(@" GROUP BY FInvalidUserName
          ) preFormInvalid ON payerAdd.FInputUserName = preFormInvalid.FInvalidUserName
                              OR payerEdit.FUpdUserName = preFormInvalid.FInvalidUserName
                              OR projectAdd.FInputUserName = preFormInvalid.FInvalidUserName
                              OR projectEdit.FUpdUserName = preFormInvalid.FInvalidUserName
                              OR preFormInput.FInputUserName = preFormInvalid.FInvalidUserName
          FULL JOIN 
    --纳税申报冲红
          ( SELECT  count(*) AS FTaxPreFormRedCount ,
                    FRedUserName
            FROM    dbo.t_TaxForm WHERE 1=1 ");
            if (beginDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FRedDate) >= 0 ");
           }
           if (endDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FRedDate) <= 0 ");
           }
            strSql.Append(@" GROUP BY FRedUserName
          ) preFormRed ON payerAdd.FInputUserName = preFormRed.FRedUserName
                          OR payerEdit.FUpdUserName = preFormRed.FRedUserName
                          OR projectAdd.FInputUserName = preFormRed.FRedUserName
                          OR projectEdit.FUpdUserName = preFormRed.FRedUserName
                          OR preFormInput.FInputUserName = preFormRed.FRedUserName
                          OR preFormInvalid.FInvalidUserName = preFormRed.FRedUserName
          FULL JOIN 
    --发票正常
          ( SELECT  count(*) AS FInvoiceFormInputCount ,
                    FInputUserName
            FROM    dbo.t_InvoiceForm WHERE 1=1 ");
            if (beginDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FInputDate) >= 0 ");
           }
           if (endDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FInputDate) <= 0 ");
           }
           strSql.Append(@" GROUP BY FInputUserName
          ) InvoiceInput ON payerAdd.FInputUserName = InvoiceInput.FInputUserName
                            OR payerEdit.FUpdUserName = InvoiceInput.FInputUserName
                            OR projectAdd.FInputUserName = InvoiceInput.FInputUserName
                            OR projectEdit.FUpdUserName = InvoiceInput.FInputUserName
                            OR preFormInput.FInputUserName = InvoiceInput.FInputUserName
                            OR preFormInvalid.FInvalidUserName = InvoiceInput.FInputUserName
                            OR preFormRed.FRedUserName = InvoiceInput.FInputUserName
          FULL JOIN 
    --发票作废
          ( SELECT  count(*) AS FInvoiceFormInvalidCount ,
                    FInvalidUserName
            FROM    dbo.t_InvoiceForm WHERE 1=1 ");
            if (beginDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FInvalidDate) >= 0 ");
           }
           if (endDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FInvalidDate) <= 0 ");
           }
           strSql.Append(@" GROUP BY FInvalidUserName
          ) InvoiceInvalid ON payerAdd.FInputUserName = InvoiceInvalid.FInvalidUserName
                              OR payerEdit.FUpdUserName = InvoiceInvalid.FInvalidUserName
                              OR projectAdd.FInputUserName = InvoiceInvalid.FInvalidUserName
                              OR projectEdit.FUpdUserName = InvoiceInvalid.FInvalidUserName
                              OR preFormInput.FInputUserName = InvoiceInvalid.FInvalidUserName
                              OR preFormInvalid.FInvalidUserName = InvoiceInvalid.FInvalidUserName
                              OR preFormRed.FRedUserName = InvoiceInvalid.FInvalidUserName
                              OR InvoiceInput.FInputUserName = InvoiceInvalid.FInvalidUserName
          FULL JOIN 
    --发票冲红
          ( SELECT  count(*) AS FInvoiceFormRedCount ,
                    FRedUserName
            FROM    dbo.t_InvoiceForm WHERE 1=1 ");
             if (beginDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + beginDate + "', FRedDate) >= 0 ");
           }
           if (endDate != "")
           {
               strSql.Append(" AND DATEDIFF(DAY, '" + endDate + "', FRedDate) <= 0 ");
           }
            strSql.Append(@" GROUP BY FRedUserName
          ) InvoiceRed ON payerAdd.FInputUserName = InvoiceRed.FRedUserName
                          OR payerEdit.FUpdUserName = InvoiceRed.FRedUserName
                          OR projectAdd.FInputUserName = InvoiceRed.FRedUserName
                          OR projectEdit.FUpdUserName = InvoiceRed.FRedUserName
                          OR preFormInput.FInputUserName = InvoiceRed.FRedUserName
                          OR preFormInvalid.FInvalidUserName = InvoiceRed.FRedUserName
                          OR preFormRed.FRedUserName = InvoiceRed.FRedUserName
                          OR InvoiceInput.FInputUserName = InvoiceRed.FRedUserName
                          OR InvoiceInvalid.FInvalidUserName = InvoiceRed.FRedUserName
        )");
           return DbHelperSQL.Query(strSql.ToString());
        }

    }
}

